/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package adapdateurs;

import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;
import moyensDeTransport.Avion;
import webservices.Distance;
import webservices.Ville;

/**
 *
 * @author BENAMMAR Riyadh
 */
public class VolsSQL implements Adapdateur{

    Statement state;
    public void connection(){
        try {
            com.mysql.jdbc.Driver driver=new Driver();
            
      String url = "jdbc:mysql://localhost:3306/billetterie";
      String user = "root";
      String passwd = "";
      Properties p=new Properties();
      p.setProperty("user", user);
      p.setProperty("password", passwd);
      
      Connection conn = driver.connect(url, p); 
      state=conn.createStatement();           
         
    } catch (Exception e) {
      e.printStackTrace();
    }
    }
    @Override
    public String traduire(String requete) {
        
        String [] champs=requete.split(":");
        String aRetourner="SELECT i.id_itineraire,i.companie, depart.point, arrive.point, h.jour, h.heure, h.minutes, i.cout FROM itineraire i, point depart, point arrive, horaire h "
                + "  where (i.id_horaire,h.id_horaire,h.jour) in (select id_horaire,id_horaire,jour from horaire where jour>="+String.valueOf(Integer.parseInt(champs[5])-31-Integer.parseInt(champs[7]))+" and "
                + "jour<="+String.valueOf(Integer.parseInt(champs[5])-31+Integer.parseInt(champs[7]))+") and (i.point_depart,depart.point) in (select id_point, point from point where point"
                + "=\'"+champs[1]+"\') and (i.point_arrive,arrive.point) in (select id_point,point from point where point=\'"+champs[3]+"\')";
        return aRetourner;
    }

    @Override
    public List<Avion> executerRequete(String requete) {
        List<Avion> resultats=new ArrayList<>();
        try {
            connection();
            System.out.println("requete: "+requete);
            
            ResultSet resultSet=state.executeQuery(requete);
            
            ResultSetMetaData metaData=resultSet.getMetaData();
            int cpt=0;
            while(resultSet.next()){
                Avion avion=new Avion();
                String depart=resultSet.getString("depart.point");
                avion.setAdresseDepart(depart);
                String arrivee=resultSet.getString("arrive.point");
                avion.setAdresseArrivee(arrivee);
                Calendar c=Calendar.getInstance();
                c.set(Calendar.DAY_OF_YEAR, resultSet.getInt("h.jour"));
                c.set(Calendar.HOUR_OF_DAY, resultSet.getInt("h.heure"));
                c.set(Calendar.MINUTE, resultSet.getInt("h.minutes"));
                avion.setDateDebut(c.getTime());
                avion.setPrix(resultSet.getInt("i.cout"));
                avion.setReseauDeTransport(resultSet.getString("i.companie"));
                // utilisation du web service pour récuperer les coordonnées de chaque ville
                Ville v1=new Ville(depart);
                Ville v2=new Ville(arrivee);
                
                Distance distance=new Distance(v1, v2);
                double dist=distance.calculDistance();
                
                Double d=new Double(avion.estimation(dist));
                int durree=d.intValue();
                avion.setDuree(durree);
                c.set(Calendar.HOUR_OF_DAY, c.get(Calendar.HOUR_OF_DAY)+durree);
                avion.setDateFin(c.getTime());
                cpt++;
                resultats.add(avion);
                c.set(Calendar.DAY_OF_YEAR, Integer.parseInt(resultSet.getString("jour")));
                System.out.println("resultat "+cpt+": jour "+c.toString()+" cout "+resultSet.getString("cout"));
            }
            
            
        } catch (SQLException ex) {
            Logger.getLogger(VolsSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return resultats;
    }
    public static void main(String[] args) {
        VolsSQL v=new VolsSQL();
        v.connection();
        String req=v.traduire("SRC:Lyon:DST:Tlemcen:DTA:324:DEC:2");
        v.executerRequete(req);
        
    }
    
}
